<?php
/* explain:		news system
 * Project:     
 * File:        news.php
 *
 * @ link 		http://www.../modules/
 * @ Email		ldmmyx@hotmail.com
 * @ copyright 	2005  Ling Deming
 * @ author 	ivan.ling  Ling Deming
 * @ version 	1.0
 *
 */
include_once("constant/GlobalConstant.php");

include_once("domain/CouponDomain.php");
include_once("domain/CouponImageDomain.php");
include_once("dao/CouponDAO.php");
include_once("dao/CommonDAO.php");

include_once("utils/CommonUtils.php");
include_once("utils/StringUtils.php");
include_once("utils/CouponUtils.php");

class CouponImpl extends CommonUtils implements CouponDAO, CommonDAO
{

	function __construct($DBTag)
	{
		parent::__construct();
		
		$this->DBTag 	= $DBTag;
		
	}

	public function getById($id) 
	{
		$where = "WHERE id = $id";
		
		return $this->getByWhere($where);
	}
	
	public function getByIdKey($idkey) 
	{
		$where = "WHERE IDkey = '$idkey'";
				
		return $this->getByWhere($where);
	}
	
	public function getByTrackingURL($trackingURL) 
	{
		$where = "WHERE trackingURL = '$trackingURL'";
		
		return $this->getByWhere($where);
	}
	
	/* get data via ID
	 *
	 * @param  int    id
	 * @return mixed  false:
	 */
	public function getByWhere($where) 
	{
		$sql  = 'SELECT id, merchantId, code, name, descript, trackingURL, trackingHtmlCode, startDate, endDate, ';
		$sql .= "sourceId, creativeType, costType, isFeatured, isRecommend, impressionpixel, createDate, createdTime, modifiedTime, status FROM coupon $where";
		$conn 	= null;
		$coupon	= null;
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn) or die("<br><br>Invalid query: " . $sql . mysql_error($conn));
			if ($row = mysql_fetch_row($rs)) {
				$coupon = new CouponDomain();

				$coupon->setId($row[0]);
				$coupon->setMerchantId($row[1]);
				$coupon->setCode($row[2]);
				$coupon->setName($row[3]);
				$coupon->setDescript($row[4]);
				
				$coupon->setTrackingURL($row[5]);
				$coupon->setTrackingHtmlCode($row[6]);
				
				$coupon->setStartDate($row[7]);
				$coupon->setEndDate($row[8]);

				$coupon->setSourceId($row[9]);
				$coupon->setCreativeType($row[10]);
				
				$coupon->setCostType($row[11]);
				
				$coupon->setIsFeatured($row[12]);
				$coupon->setIsRecommend($row[13]);
				$coupon->setImpressionpixel($row[13]);
				
				$coupon->setCreateDate($row[14]);
				
				$coupon->setCreatedTime($row[16]);
				$coupon->setModifiedTime($row[17]);
				
				$coupon->setStatus($row[18]);
			} else {
				$this->messages = "get coupon data failed by Id. sql : $sql <br>";
				$this->messages .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->messages = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $coupon;
	}

	public function getCouponIdKey($merId, $couponCode, $couponName, $couponDescription)
	{
		return md5($merId . $couponCode . $couponName . $couponName);
	}
	
	public function add($coupon)
	{
		if($coupon instanceof CouponDomain){
			$IDkey = $this->getCouponIdKey($coupon->merchantId, $coupon->code, $coupon->name, $coupon->descript);
			
			$oDbCoupon = $this->getByIdKey($IDkey);
			if(is_object($oDbCoupon)){
				$isUpdate = false;
				
//				echo "recommend: " .$oDbCoupon->isRecommend. "\n";
//				echo "db getCreativeType: " .$oDbCoupon->creativeType. "\n";
//				echo "getCreativeType: " .$coupon->creativeType. "\n";
				
				if($oDbCoupon->isRecommend == 1){
					$isUpdate = false;
				}else if($oDbCoupon->creativeType == 1){
					$oImage = $this->getCouponImage($oDbCoupon->id);
					
					$couponImg = $coupon->getCouponImage();
					if(is_object($couponImg) && is_object($oImage)){
//						echo "db image width: " .$oImage->width. "\n";
//						echo "feed image width: " .$couponImg->width. "\n";
						
						if($couponImg->width > $oImage->width){
							$isUpdate = true;
						}						
					}
				}else if($coupon->creativeType == 1 && $oDbCoupon->creativeType == 2){
					$isUpdate = true;
				}
								
				if(!$isUpdate){
//					echo "2222\n";
									
					$this->message = 'coupon is existed.';
					return -2;
				}
			}

//echo "3333\n";
			$sql 	 = "INSERT INTO coupon SET createdTime = UTC_TIMESTAMP(), modifiedTime = UTC_TIMESTAMP()";
			
			$descript = $coupon->getDescript();
			if(!empty($descript)){
				$sql	.= ", descript='";
				$sql	.= addslashes($descript);
				$sql	.= "'";
			}
			
			$sql 	.= ", name='";
			$sql	.= addslashes($coupon->getName());
	
			$sql 	.= "', merchantId=";
			$sql	.= $coupon->getMerchantId();
	
			$sql 	.= ", sourceId=";
			$sql	.= $coupon->getSourceId();

			$startDate = $coupon->getStartDate();
			if(!empty($startDate)){
				$sql	.= ", startDate='";
				$sql	.= date('Y-m-d', strtotime($startDate));
				$sql	.= "'";
			}
			
			$endDate = $coupon->getEndDate();
			if(!empty($endDate)){
				$sql	.= ", endDate='";
				$sql	.= date('Y-m-d', strtotime($endDate));
				$sql	.= "'";
			}
			
			$createDate = $coupon->getCreateDate();
			if(!empty($createDate)){
				$sql	.= ", createDate='";
				$sql	.= date('Y-m-d', strtotime($createDate));
				$sql	.= "'";
			}
			
			$sql	.= ", creativeType='";
			$sql	.= $coupon->getCreativeType();

			$sql	.= "', code='";
			$sql	.= $coupon->getCode();

			$sql	.= "', costType='";
			$sql	.= $coupon->getCostType();

			$sql	.= "', trackingURL='";
			$sql	.= addslashes($coupon->getTrackingURL());

			$sql	.= "', impressionpixel='";
			$sql	.= addslashes($coupon->impressionpixel);
		
			$sql	.= "', linkCodeJavascript='";
			$sql	.= addslashes($coupon->linkCodeJavascript);
			
			
			$sql	.= "', trackingHtmlCode='";
			$sql	.= addslashes($coupon->trackingHtmlCode);			
			
			$sql	.= "', IDkey='";
			$sql	.= $IDkey;
			
			$sql	.= "', status=";
			$sql	.= $coupon->getStatus();
			$sql	.= " ON DUPLICATE KEY UPDATE descript=Values(descript), name=Values(name), merchantId=Values(merchantId), sourceId=Values(sourceId)" .
					", startDate=Values(startDate), endDate=Values(endDate), createDate=Values(createDate), creativeType=Values(creativeType), code=Values(code), " .
					"costType=Values(costType), trackingURL=Values(trackingURL), linkCodeJavascript=Values(linkCodeJavascript), trackingHtmlCode=Values(trackingHtmlCode)," .
					"impressionpixel=Values(impressionpixel), modifiedTime=Values(modifiedTime)";

			$id		= 0;
			$conn 	= null;
			
			try{
				$conn 	= $this->getConnection($this->DBTag);
				$rs  	= mysql_query($sql, $conn) or die("Invalid query: " . $sql . mysql_error($conn));
				
				if($rs){
					if(is_object($oDbCoupon))
						$id = $oDbCoupon->getId();
					else
						$id = mysql_insert_id($conn);
					
					$coupon->setId($id);
					
					$couponImg = $coupon->getCouponImage();
					if(is_object($couponImg)){
						$coupon->getCouponImage()->setCouponId($id);
					
						$this->addImage($coupon->getCouponImage());						
					}
					
					$this->messages = "add successfully. id = $id";
				}else{
					$this->messages  = "add failed. sql : $sql . \n";
					$this->messages .= "error: " .  mysql_error($conn);
				}
			}catch(Exception $e){
				$this->messages = $e->getMessage();
				
				$id	= -1;
			}
			
			$this->freeConnection($this->DBTag, $conn);
		}else{
			$id	= -1;
			
			$this->message = 'input object is not instance of CouponDomain.';
		}	
		
		return $id;
	}

	
	public function addAttribute($couponId, $attributeId, $attributeValue)
	{
		$sql 	 = "INSERT INTO coupon_attributes SET couponId=";
		$sql	.= $couponId;
		
		$sql 	.= ", attributeId=";
		$sql	.= $attributeId;
		
		$sql 	.= ", attributeValue='";
		$sql	.= addslashes($attributeValue);

		$sql	.= "' ON DUPLICATE KEY UPDATE attributeValue=Values(attributeValue)";
		
		return $this->insert($this->DBTag, $sql);		
	}	
		
	public function addCategory($couponId, $categoryId)
	{
		$sql 	 = "INSERT INTO coupon_category SET couponId=";
		$sql	.= $couponId;
		
		$sql 	.= ", categoryId=";
		$sql	.= $categoryId;

		$sql	.= " ON DUPLICATE KEY UPDATE couponId=Values(couponId)";
		
		return $this->insert($this->DBTag, $sql);
	}
	
	public function getCategoryId($couponId)
	{
		$sql 	 = "SELECT categoryId FROM coupon_category WHERE couponId=$couponId";
		
		return $this->getColumn($this->DBTag, $sql);
	}
	
	public function addImage($couonImage)
	{
		$id	= -1;
		
		if($couonImage instanceof CouponImageDomain){
			$sql 	 = "INSERT INTO coupon_images SET modifiedTime = UTC_TIMESTAMP(), couponId=";
			$sql	.= $couonImage->getCouponId();
			
			$sql 	.= ", name='";
			$sql	.= addslashes($couonImage->getName());
	
			$sql 	.= "', impressionTrackingUrl='";
			$sql	.= $couonImage->getImpressionTrackingUrl();

			$sql	.= "', imageAltText='";
			$sql	.= addslashes($couonImage->getImageAltText());

			$sql	.= "', width='";
			$sql	.= $couonImage->getWidth();

			$sql	.= "', height='";
			$sql	.= $couonImage->getHeight();

			$sql	.= "', useType='";
			$sql	.= $couonImage->getUseType();
				
			$sql	.= "', status=";
			$sql	.= $couonImage->getStatus();
			$sql	.= " ON DUPLICATE KEY UPDATE couponId=Values(couponId), name=Values(name), impressionTrackingUrl=Values(impressionTrackingUrl), imageAltText=Values(imageAltText)" .
					", width=Values(width), height=Values(height), modifiedTime=Values(modifiedTime)";
			
			$id = $this->insert($this->DBTag, $sql);
			
			$couonImage->setId($id);
			
			$this->setUseImage($couonImage->getCouponId());
		}else{			
			$this->message = 'input object is not instance of CouponDomain.';
		}
		
		return $id;
	}
	
	/* delete news class
	 *
	 * @param  int    	id
	 * @param  string  	status
	 * @param  return 	boolean
	 */
	public function delete($id)
	{
		$sql = "DELETE FROM coupon WHERE id = $id";
		
		return $this->update($this->DBTag, $sql);
	}
	
	public function isExisted($IDkey)
	{
		$sql  = "SELECT id FROM coupon WHERE IDKey = '$IDkey'";
		$conn = null;
		
		try {
			$conn = $this->getConnection($this->DBTag);
			
			$rs = mysql_query($sql, $conn);
			if ($row = mysql_fetch_row($rs)) {
				return true;
			} else {
				return false;
			}
		} catch (Exception $e) {
			$this->messages = $e->getMessage();

			return false;
		}
		
		$this->freeConnection($this->DBTag, $conn);
	}
	
	public function setFeatured($couponId, $r_sortValue, $isFeatured = 1) 
	{
		$sql  = "UPDATE coupon SET isFeatured = $isFeatured, r_sortValue = $r_sortValue WHERE id = $couponId";
				
		return $this->update($this->DBTag, $sql);			
	}
	
	public function setFeaturedByMerchantId($merchantId, $isFeatured = 0) 
	{
		$sql  = "UPDATE coupon SET isFeatured = $isFeatured, r_sortValue = 0 WHERE merchantId = $merchantId";
		
		return $this->update($this->DBTag, $sql);
	}
	
	public function setRecommend($couponId, $isRecommend) 
	{
		$sql  = "UPDATE coupon SET isRecommend = $isRecommend WHERE id = $couponId";
		
		return $this->update($this->DBTag, $sql);
	}
	
	public function setFeaturedByCategoryId($merchantId, $categoryId, $isFeatured = 0) 
	{
		$sql  = "UPDATE coupon SET isFeatured = $isFeatured, r_sortValue = 0 WHERE merchantId = $merchantId AND id IN (SELECT couponId FROM coupon_category WHERE categoryId in ($categoryId))";
		
		return $this->update($this->DBTag, $sql);
	}
	
	public function getCouponImage($couponId, $useType = 1) 
	{
		$sql  = 'SELECT id, name, width, height, useType, impressionTrackingUrl, imageAltText ';
		$sql .= "FROM coupon_images WHERE couponId = $couponId AND useType = $useType";
		
		$conn	= null;
		$coupon = null;
		
		try {
			$conn 	= $this->getConnection($this->DBTag);
			$rs 	= mysql_query($sql, $conn);
			if ($rs && $row = mysql_fetch_row($rs)) {
				$coupon = new CouponImageDomain();
				$coupon->setCouponId($couponId);
				
				$coupon->setId($row[0]);
				$coupon->setName($row[1]);
				$coupon->setWidth($row[2]);
				$coupon->setHeight($row[3]);
				$coupon->setUseType($row[4]);
				$coupon->setImpressionTrackingUrl($row[5]);
				$coupon->setImageAltText($row[6]);

				
			} else {
				$this->messages = "get coupon data failed by Id. sql : $sql \n";
				$this->messages .= "error: " . mysql_error($conn);
			}
		} catch (Exception $e) {
			$this->messages = $e->getMessage();
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $coupon;
	}
	
	public function setUseImage($couponId)
	{
		$sql		= "SELECT id, width, height FROM coupon_images WHERE status = 1 AND couponId = $couponId AND (useType = 0 OR useType = 1)";
		$images 	= $this->getRows($this->DBTag, $sql);
				
		$arrImages = array();
		foreach($images as $rows){
			$maxWH 		= max($rows['width'], $rows['height']);
			
			$imageId	= $rows['id'];
			
			$arrImages[$maxWH] = $imageId;
		}
		
		if(count($arrImages) > 0){
			krsort($arrImages);
		
			$imageId = array_shift($arrImages);
			
			$sql	= "UPDATE coupon_images SET useType = 0 WHERE useType = 1 AND couponId = $couponId";
			$this->update($this->DBTag, $sql);

			$sql	= "UPDATE coupon_images SET useType = 1 WHERE id = $imageId";
			$this->update($this->DBTag, $sql);
			
			$sql	= "UPDATE coupon SET status = 1 WHERE id = $couponId";
			$this->update($this->DBTag, $sql);
		}else{
			// not image status
			$sql	= "UPDATE coupon SET status = 3 WHERE id = $couponId";
			$this->update($this->DBTag, $sql);
		}
	}
	
	public function getCoupons($page = 0, $discount = 20)
	{
		//TODO remove sort
		return $this->getCustoms(" WHERE status = 1", " order by id desc", $page, $discount);
	}
	
	public function getCommonCoupons($merchantId, $page = 0, $discount = 20, $order = " ORDER BY r_sortValue DESC")
	{
		return $this->getCustoms(" WHERE status = 1 AND isFeatured != 1 AND isRecommend != 1 AND merchantId = $merchantId", $order, $page, $discount);
	}
	
	public function getAutoRecommendedCoupons($merchantId, $page = 0, $discount = 20, $order = " ORDER BY r_sortValue DESC")
	{
		//TODO remove sort
		return $this->getCustoms(" WHERE status = 1 AND isRecommend = 2 AND merchantId = $merchantId", $order, $page, $discount);
	}
	
	public function getRecommendedCoupons($merchantId, $page = 0, $discount = 20, $order = " ORDER BY r_sortValue DESC")
	{
		//TODO remove sort
		return $this->getCustoms(" WHERE status = 1 AND isRecommend = 1 AND merchantId = $merchantId", $order, $page, $discount);
	}
	
	public function getFeaturedCoupons($merchantId, $page = 0, $discount = 20, $order = " ORDER BY r_sortValue DESC")
	{
		//TODO remove sort
		return $this->getCustoms(" WHERE status = 1 AND isFeatured = 1 AND merchantId = $merchantId", $order, $page, $discount);
	}
	
	public function getCouponsByMerchant($merchantId, $page = 0, $discount = 20, $order = " ORDER BY startDate DESC, r_sortValue DESC")
	{
		return $this->getCustoms(" WHERE status = 1 AND merchantId = $merchantId", $order, $page, $discount);
	}
	
	public function getCouponsByCategoryId($merchantId, $categoryId, $page = 0, $discount = 20, $order = " ORDER BY r_sortValue DESC")
	{
		return $this->getCustoms(" WHERE status = 1 AND merchantId = $merchantId AND id IN (SELECT couponId FROM coupon_category WHERE categoryId in ($categoryId))", $order, $page, $discount);
	}
	
	public function getCouponsByTag($tagId, $page = 0, $discount = 20, $order = " order by id desc")
	{
		//TODO remove sort
		return $this->getCustoms(" WHERE status = 1 AND id IN (SELECT couponId FROM coupon_tag WHERE authorized = 1 AND tagId = $tagId)", $order, $page, $discount);
	}
	
	public function getAll()
	{
		return $this->getCustoms(" WHERE status = 1", '', 0, 0);
	}
	
	public function getCustoms($where, $order, $page = 0, $discount = 20)
	{
		$arrback 	= array();
		$conn		= null;	
		
		try{
			$conn 	= $this->getConnection($this->DBTag);
			
			$sql 	= "SELECT count(1) FROM coupon $where";
			$rs  	= mysql_query($sql, $conn);
			
			list($total) = mysql_fetch_row($rs);
			
			$this->mutilPager($total, $page, $discount);
			if(!empty($total)){
				$sql  = 'SELECT id, merchantId, code, name, descript, trackingURL, trackingHtmlCode, startDate, endDate, ';
				$sql .= "sourceId, creativeType, costType, isFeatured, isRecommend, impressionpixel, createDate, createdTime, modifiedTime, status FROM coupon $where $order";
				
				if($discount > 0)
					$sql .= " LIMIT $this->firstcount, $discount";

				$rs  = mysql_query($sql, $conn);
				if($rs){
					while($row = mysql_fetch_array($rs)){
						$coupon = new CouponDomain();

						$coupon->setId($row[0]);
						$coupon->setMerchantId($row[1]);
						$coupon->setCode($row[2]);
						$coupon->setName($row[3]);
						$coupon->setDescript($row[4]);
						
						$coupon->setTrackingURL($row[5]);
						$coupon->setTrackingHtmlCode($row[6]);
						
						$coupon->setStartDate($row[7]);
						$coupon->setEndDate($row[8]);
						
						$coupon->setSourceId($row[9]);
						$coupon->setCreativeType($row[10]);						
						
						$coupon->setCostType($row[11]);
						
						$coupon->setIsFeatured($row[12]);
						$coupon->setIsRecommend($row[13]);
						
						$coupon->setImpressionpixel($row[14]);
						
						$coupon->setCreateDate($row[15]);
						
						$coupon->setCreatedTime($row[16]);
						$coupon->setModifiedTime($row[17]);
						
						$coupon->setStatus($row[18]);
						
						$arrback[] = $coupon;
					}
				}else {
					$this->message = "select failed. sql : $sql";
					$this->message .= "\nerror: " . mysql_error($conn);

				}
			}
		}catch(Exception $e){
			$this->message = $e->getMessage();			
		}
		
		$this->freeConnection($this->DBTag, $conn);
		return $arrback;
	}
	
	public function getCouponByGoogleLink($merchantId, $link)
	{
		$creativeType 	= $this->convertToCreative($link);
		$couponCode		= $this->pasreCouponCode($link);		
		
		$oC = new CouponDomain();		
		
		$oC->setMerchantId($merchantId);
		$oC->setCode($couponCode);
		$oC->setName($link->getName());
		$oC->setDescript($link->getDescription());
		
		$oC->setTrackingURL($link->getClickTrackingUrl);
		$oC->setTrackingHtmlCode('');
		
		$oC->setStartDate($link->getStartDate());
		$oC->setEndDate($link->getEndDate());
		
		$oC->setSourceId(GlobalConstant::SOURCE_ID_GOOGLE);
		$oC->setCreativeType($creativeType);
		
		$oC->setimpressionTrackingUrl($link->getImpressionTrackingUrl());
		
		$oC->setCostType(GlobalConstant::COST_TYPE_CPA);
		
		$oC->setIsFeatured(0);
		$oC->setIsRecommend(0);
	}
	
	private function convertToCreative($link)
	{
		$creativeType = $link->getLinkType();
		
		if($creativeType == 'BANNER'){
			$creativeType = '1';
		}else if ($creativeType == 'TEXT'){
			$creativeType = '2';
		}else if ($creativeType == 'SEARCH BOX'){
			$creativeType = '3';			
		}else
			$creativeType = '2';
			
		return $creativeType;
	}
	
	private function pasreCouponCode($link)
	{
		$code = '';
		
		if ($link->getPromotionType() == 'COUPON'){
			$startFlag 	= 'with coupon code ';
			$endFlag	= '. expirat';
			
			$code 	= StringUtils::getCutValue($startFlag, $endFlag, $link->getName());
			$name 	= StringUtils::getStartValue(' with coupon code', $link->getName());
		}
		
		if(empty($code)){
			$code = CouponUtils::findCode($link->getName());		
		}
		
		return $code;
	}
	
	public function getMessage() 
	{
		return $this->message;
	}
	
}
?>
